import datetime

import pymysql as sql

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllCopeRecord():
    print('return all cope_record')

    sql = "select risk_cope.risk_cope_id, risk_cope.time, risk_cope.risk_id, riskpoint.name, risk_cope.status " \
          "from risk_cope, riskpoint " \
          "where risk_cope.risk_id = riskpoint.risk_id"

    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_id', 'time', 'risk_id', 'name', 'status']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['time'] = dic['time'].strftime("%Y-%m-%d")
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def addRiskCopeToDB(risk_id):
    print('insert a new risk cope')

    riskCope_id = newRiskCopeID()
    date = datetime.datetime.now().strftime("%Y-%m-%d")

    sql = "insert into risk_cope values('%s', '%s', '%s', '%d')" % (str(riskCope_id), date, str(risk_id), 0)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def newRiskCopeID():
    sql = "select max(risk_cope_id) from risk_cope"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str


def getRiskForCopeFromDB():
    print('return all risk_id and name')

    sql = "select riskpoint.risk_id, riskpoint.name " \
          "from riskpoint " \
          "where riskpoint.risk_id not in  ( " \
          "select risk_id " \
          "from risk_cope " \
          ")"
    print(sql)
    cursor.execute(sql)

    attribute = ['risk_id', 'name']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def searchCopeRecord(word):
    sql = "select risk_cope.risk_cope_id, risk_cope.time, risk_cope.risk_id, riskpoint.name, risk_cope.status " \
          "from risk_cope, riskpoint " \
          "where risk_cope.risk_id = riskpoint.risk_id " \
          "and name LIKE '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_id', 'time', 'risk_id', 'name', 'status']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['time'] = dic['time'].strftime("%Y-%m-%d")
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def deleteCopeRecordFromDB(word):
    sql = "Delete FROM risk_cope WHERE risk_cope_id ='%s'" % str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getCopeWithCopeIDFromDB(risk_cope_id):
    sql = "select risk_cope_id, time, risk_cope.risk_id, name, status " \
          "from risk_cope, riskpoint " \
          "where risk_cope.risk_id = riskpoint.risk_id " \
          "and risk_cope_id = '%s'" % risk_cope_id
    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_id', 'time', 'risk_id', 'name', 'status']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['time'] = dic['time'].strftime("%Y-%m-%d")
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l
